# encoding    : utf-8
# author      : 伦裕如
# project     : hrjt_-py1
# file        : mySqlManage
# time        : 2024-06-20 上午8:40 

import pymysql


class MysqlManage:

    SELECT_SQL = "select %s from %s where %s"
    INSERT_SQL = "insert into %s (%s) values (%s)"
    DELETE_SQL = "delete from %s where %s"

    def __init__(self, database = 'jiangsu', user = 'root', password = 'root',
                 host = 'localhost', port = 3306, charset = 'utf8'):
        """
        MySql连接管理器初始化
        :param user:        用户
        :param password:    密码
        :param database:    数据库
        :param host:        数据库地址
        :param port:        数据库端口号
        :param charset:     数据库编码方式
        """
        self.user = user
        self.password = password
        self.database = database
        self.host = host
        self.port = port
        self.charset = charset
        self.conn = None
        self.cursor = None

    def connect(self) -> bool:
        """
        连接数据库并创建游标
        :return: 操作是否成功
        """
        try:
            self.conn = pymysql.connect(
                host = self.host,
                port = self.port,
                user = self.user,
                password = self.password,
                database = self.database,
                charset = self.charset,
            )
            self.cursor = self.conn.cursor()
            return True
        except Exception as e:
            print('connect mysql error:', e)
            self.conn = None
            self.cursor = None
            return False

    def close_conn(self):
        """
        关闭数据库连接
        :return:
        """
        if self.conn is not None:
            self.conn.close()
            self.conn = None

    def close_cursor(self):
        """
        关闭游标
        :return:
        """
        if self.cursor is not None:
            self.cursor.close()
            self.cursor = None

    def execute(self, sql):
        """
        执行sql语句
        :param sql: 需要执行的sql语句
        :return:
        """
        try:
            if self.conn is None:
                if not self.connect():
                    raise Exception('connect mysql error')
            elif self.cursor is None:
                self.cursor = self.conn.cursor()
            self.cursor.execute(sql)
            self.conn.commit()
        except Exception as e:
            print('execute sql error:', e)

    def update(self, table, column_value: dict, where):
        """
        更新操作
        :param table: 更新表名
        :param column_value: 更新字段，键值对存放 字段名为 Key， 值为 value
        :param where: 更新条件
        :return:
        """
        sql_prev = "update %s set" % table
        sql_cur = []
        for column, value in column_value.items():
            sql_cur.append(f'{column} = {value}')
        sql = sql_prev + ', '.join(sql_cur) + 'where ' + where
        self.execute(sql)
        self.close_cursor()

    def insert(self, table, column, value):
        """
        插入数据
        :param table: 表名
        :param column: 字段名
        :param value: 插入数据
        :return:
        """
        sql = self.INSERT_SQL % (table, column, value)
        self.execute(sql)
        self.close_cursor()

    def select(self, table, column, where):
        """
        查询操作
        :param table: 表名
        :param column:  字段名
        :param where: 查询条件
        :return: 查询结果
        """
        try:
            sql = self.SELECT_SQL % (column, table, where)
            self.execute(sql)
            return self.cursor.fetchall()
        finally:
            self.close_cursor()

    def select_custom(self, sql):
        try:
            self.execute(sql)
            return self.cursor.fetchall()
        finally:
            self.close_cursor()

    def delete(self, table, where):
        """
        删除操作
        :param table: 表名
        :param where: 删除条件
        :return:
        """
        sql = self.DELETE_SQL % (table, where)
        self.execute(sql)
        self.close_cursor()
